package com.sencorsta.ids.core.data.mysql;


import java.math.BigDecimal;
import java.sql.*;
import java.util.*;
import java.util.Date;
import java.util.concurrent.CountDownLatch;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;

import com.alibaba.druid.pool.DruidPooledConnection;
import com.alibaba.druid.pool.GetConnectionTimeoutException;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException;
import com.sencorsta.ids.core.configure.GlobalConfigure;
import com.sencorsta.ids.core.configure.SysConfig;
import com.sencorsta.ids.core.log.Out;


/**
 * @author ICe
 */
public class DBDao {


    public boolean test() {
        DBPoolConnection dbp = DBPoolConnection.getInstance();    //获取数据连接池单例
        DruidPooledConnection conn = null;
        PreparedStatement ps = null;
        try {
            conn = dbp.getConnection();
            ps = conn.prepareStatement("select 1");
            ps.executeQuery();
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } finally {
            try {
                if (null != ps) {
                    ps.close();
                }
                if (null != conn) {
                    conn.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return true;
    }

    public int execute(String sql, Object... args) {
        Out.trace("execute => ", transSql(sql, args));
        DBPoolConnection dbp = DBPoolConnection.getInstance();    //获取数据连接池单例
        DruidPooledConnection conn = null;
        PreparedStatement ps = null;
        try {
            conn = dbp.getConnection();
            ps = conn.prepareStatement(sql);
            setParams(ps, args);
            return ps.executeUpdate();
        }catch (GetConnectionTimeoutException e) {
            Out.warn(Thread.currentThread().getName()+"execute => " + "数据库链接获取超时！1秒后重试 ");
            try {
                Thread.sleep(1000);
            } catch (InterruptedException exc) {
                e.printStackTrace();
            }
            execute(sql,args);
        }
        catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (null != ps) {
                    ps.close();
                }
                if (null != conn) {
                    conn.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return -1;
    }

    public int executeMultiple(JSONArray list) {
        Out.trace("executeMultiple => ", list.size());
        DBPoolConnection dbp = DBPoolConnection.getInstance();    //获取数据连接池单例
        DruidPooledConnection conn = null;
        PreparedStatement ps = null;
        int count = 0;
        try {
            conn = dbp.getConnection();
            conn.setAutoCommit(false);
            conn.setSavepoint();
            Out.trace("事务开始！ ");
            for (int i = 0; i < list.size(); i++) {
                JSONObject json = list.getJSONObject(i);
                String sql = json.getString("SQL");
                Object[] args = json.getJSONArray("args").toArray();
                Out.trace("execute => ", transSql(sql, args));
                ps = conn.prepareStatement(sql);
                setParams(ps, args);
                ps.executeUpdate();
                count++;
//                if (i==5){
//                    throw new SQLException("人工报错");
//                }
            }
            if (count == list.size()) {
                conn.commit();
            }
            Out.trace("事务结束！ ");
            return count;
        }
        catch (GetConnectionTimeoutException e) {
            Out.warn("executeMultiple => " + "数据库链接获取超时！1秒后重试 ");
            try {
                Thread.sleep(1000);
            } catch (InterruptedException exc) {
                e.printStackTrace();
            }
            executeMultiple(list);
        }
        catch (MySQLTransactionRollbackException ex) {
            //出现事务锁等待300秒再次执行
            Out.warn("executeMultiple => " + "事务死锁！300毫秒后重试 ");
            try {
                conn.rollback();
                Out.warn("事务回滚！ ");
            } catch (SQLException exc) {
                ex.printStackTrace();
            }finally {
                try {
                    Thread.sleep(300);
                } catch (InterruptedException exc) {
                    ex.printStackTrace();
                }
                executeMultiple(list);
            }
        }catch (SQLException e) {
            Out.error("executeMultiple => "+"sql报错:"+e.getMessage());
            e.printStackTrace();
            try {
                conn.rollback();
                Out.warn("事务回滚！ ");
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        } finally {
            try {
                if (null != ps) {
                    ps.close();
                }
                if (null != conn) {
                    conn.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return -1;
    }

    public int executeKey(String sql, Object... args) {
        Out.trace("executeKey => ", transSql(sql, args));
        DBPoolConnection dbp = DBPoolConnection.getInstance();    //获取数据连接池单例
        DruidPooledConnection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = dbp.getConnection();
            ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            setParams(ps, args);
            if (ps.executeUpdate() > 0) {
                rs = ps.getGeneratedKeys();
                if (rs.next()) {
                    return rs.getInt(1);
                }
            }
        }
        catch (GetConnectionTimeoutException e) {
            Out.warn("executeKey => " + "数据库链接获取超时！1秒后重试 ");
            try {
                Thread.sleep(1000);
            } catch (InterruptedException exc) {
                e.printStackTrace();
            }
            executeKey(sql,args);
        }
        catch (SQLException e) {
            e.printStackTrace();
            return -1;
        } finally {
            try {
                if (null != ps) {
                    ps.close();
                }
                if (null != conn) {
                    conn.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return -1;
    }


    public <T> List<T> query(String sql, DBRowMapper<T> rowMapper, Object... args) {
        Out.trace("query => ", transSql(sql, args));
        DBPoolConnection dbp = DBPoolConnection.getInstance();    //获取数据连接池单例
        List<T> values = new ArrayList<T>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = dbp.getConnection();
            ps = conn.prepareStatement(sql);
            setParams(ps, args);
            rs = ps.executeQuery();
            while (rs.next()) {
                values.add(rowMapper.mapRow(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } finally {
            try {
                if (null != ps) {
                    ps.close();
                }
                if (null != conn) {
                    conn.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return values;
    }


    private void setParams(PreparedStatement pstmt, Object[] args) throws SQLException {
        if (args == null) return;
        for (int i = 0; i < args.length; i++) {
            if (args[i] instanceof Integer) {
                pstmt.setInt(i + 1, (Integer) args[i]);
            } else if (args[i] instanceof String) {
                pstmt.setString(i + 1, (String) args[i]);
            } else if (args[i] instanceof Byte) {
                pstmt.setByte(i + 1, (Byte) args[i]);
            } else if (args[i] instanceof Boolean) {
                pstmt.setByte(i + 1, (byte) (((Boolean) args[i]) ? 1 : 0));
            } else if (args[i] instanceof Short) {
                pstmt.setShort(i + 1, (Short) args[i]);
            } else if (args[i] instanceof Timestamp) {
                pstmt.setTimestamp(i + 1, (Timestamp) args[i]);
            } else if (args[i] instanceof Date) {
                pstmt.setTimestamp(i + 1, new java.sql.Timestamp(((Date) args[i]).getTime()));
            } else if (args[i] instanceof java.sql.Date) {
                pstmt.setDate(i + 1, new java.sql.Date(((Date) args[i]).getTime()));
            } else if (args[i] instanceof Float) {
                pstmt.setFloat(i + 1, (float) args[i]);
            } else if (args[i] instanceof Double) {
                pstmt.setDouble(i + 1, (Double) args[i]);
            } else if (args[i] instanceof byte[]) {
                pstmt.setBytes(i + 1, (byte[]) args[i]);
            } else if (args[i] instanceof Long) {
                pstmt.setLong(i + 1, (Long) args[i]);
            } else if (args[i] instanceof BigDecimal) {
                pstmt.setDouble(i + 1, ((BigDecimal) args[i]).doubleValue());
            } else {
                if (args[i] != null) {
                    Out.warn("暂未支持的持久化数据类型，请确认：", args[i].getClass());
                }
                pstmt.setObject(i + 1, args[i]);
            }
        }
    }

    public static void main(String[] args) throws InterruptedException {
        SysConfig.getInstance().init();
//        for (int i = 0; i <3000 ; i++) {
//            new Thread(new Runnable() {
//                @Override
//                public void run() {
//                    int j=0;
//                    while (j<10) {
//                        DBDao dao = new DBDao();
//                        String sql = "insert into human (name) values(" + new Random().nextInt() + ")";
//                        dao.insert(sql);
//                        j++;
//                    }
//                }
//            }).start();

//        }
        HashMap<String, String> properties = new HashMap<>();
        properties.put("url", "jdbc:mysql://192.168.0.188:3306/test?characterEncoding=utf8");
        properties.put("username", "root");
        properties.put("password", "1qaz@WSX123");

//        properties.put("url", "jdbc:mysql://192.168.3.3:3306/test?characterEncoding=utf8");
//        properties.put("username", "root");
//        properties.put("password", "0825");
        try {
            DBPoolConnection.getInstance().init(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }
        long startTime = new Date().getTime();


//        for (int i = 0; i < 10000; i++) {
//            {
//                DBDao dao = new DBDao();
//                String sql = "insert into human (name,update_time) values(?,?)";
//                Object[] parms = new Object[]{new Random().nextInt(), new Date()};
//                dao.execute(sql, parms);
//            }
//        }

//        {
//            JSONArray list = new JSONArray();
//            for (int i = 0; i < 10000; i++) {
//                String sql = "insert into human (name,update_time) values(?,?)";
//                Object[] parms = new Object[]{"事务：" + new Random().nextInt(), new Date()};
//                JSONObject object = new JSONObject();
//                object.put("SQL", sql);
//                object.put("args", parms);
//                list.add(object);
//            }
//            dao.executeMultiple(list);
//        }

//        ExecutorService __EXECUTOR__ = Executors.newCachedThreadPool();
//        for (int i = 0; i < 10000; i++) {
//            __EXECUTOR__.execute(()->{
//                DBDao dao = new DBDao();
//                String sql = "insert into human (name,update_time) values(?,?)";
//                Object[] parms = new Object[]{new Random().nextInt(), new Date()};
//                dao.execute(sql, parms);
//            });
//            if (i==9999)
//            Out.info("now");
//        }

        CountDownLatch latch = new CountDownLatch(1000);
        //ExecutorService __EXECUTOR__ = Executors.newCachedThreadPool();
        Out.info("cores:" + Runtime.getRuntime().availableProcessors());
        ExecutorService __EXECUTOR__ = Executors.newFixedThreadPool(Runtime.getRuntime().availableProcessors()*2+1);
//        ExecutorService __EXECUTOR__ = Executors.newCachedThreadPool();
//        ExecutorService __EXECUTOR__ = Executors.newFixedThreadPool(1);

        ExecutorService __EXECUTOR__2 = Executors.newFixedThreadPool(Runtime.getRuntime().availableProcessors()*2+1);
//        ExecutorService __EXECUTOR__2 = Executors.newFixedThreadPool(8);
//        for (int i = 0; i < 5000; i++) {
//            __EXECUTOR__.execute(() -> {
//                    DBDao dao = new DBDao();
//                    String sql = "insert into human (name,update_time) values(?,?)";
//                    Object[] parms = new Object[]{new Random().nextInt(), new Date()};
//                    dao.execute(sql, parms);
//                    latch.countDown();
//            });
//        }
//        System.out.println("Mid:");
//        for (int i = 0; i < 5000; i++) {
//            __EXECUTOR__2.execute(() -> {
//                DBDao dao = new DBDao();
//                String sql = "insert into human (name,update_time) values(?,?)";
//                Object[] parms = new Object[]{new Random().nextInt(), new Date()};
//                dao.execute(sql, parms);
//                latch.countDown();
//            });
//        }

        for (int i = 0; i < 1000; i++) {
            __EXECUTOR__.execute(() -> {
                DBDao dao = new DBDao();
                String sql = "insert into human (name,update_time) values(?,?)";
                Object[] parms = new Object[]{new Random().nextInt(), new Date()};
                dao.execute(sql, parms);
                latch.countDown();
                //Out.info(Thread.currentThread().getName());
            });
        }
        latch.await();

        //
//        {
//            String sql = "select * from human  where id<?";
//            Object[] parms = new Object[]{4426290};
//
//            List<JSONObject> list = dao.query(sql, new DBJSONRow(), parms);
//            System.out.println(JSON.toJSONString(list));
//
//            List<Temp> list2 = dao.query(sql, new DBObjectRow(), parms);
//            System.out.println(JSON.toJSONString(list2));
//        }
        long endTime = new Date().getTime();
        System.out.println("Over:" + (endTime - startTime));
    }

    class Temp {
        public int id;
        public String name;
    }

    private String transSql(String sql, Object[] parameters) {

        if (sql.indexOf("?") < 0) {
            return sql;
        }

        for (int i = 0; i < parameters.length; i++) {
            sql = sql.replaceFirst("\\?", parameters[i] != null ? "\'"
                    + parameters[i].toString() + "\'" : "NULL");
        }
        return sql;
    }
}